/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sims.manager_SalesPerson;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import sims.databaseconnection.Connector;
import sims.entity.Order;
import sims.entity.OrderDetail;
import sims.entity.Product;
import sims.entity.User;

/**
 *
 * @author vanthanh
 */
public class Function {

    public ArrayList<Product> get_All_Item() {
        try {
            ArrayList<Product> arrItem = new ArrayList<Product>();
            Connector condb = new Connector();
            Connection con = condb.connect();

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from item");

            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("itemId"));
                product.setProductName(rs.getString("itemName"));
                product.setCatID(rs.getString("catId"));
                product.setUnit(rs.getString("unit"));
                product.setQuantity(rs.getInt("quantityAvailable"));
                product.setQuantityEOD(rs.getInt("quantityEndOfDay"));
                product.setPrice(rs.getFloat("price"));
                arrItem.add(product);
            }
            st.close();
            con.close();
            return arrItem;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public String getCatName(int catId) {
        try {

            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select catName from category where catId=" + catId + "");
            rs.next();
            String catName = rs.getString("catName");
            con.close();
            return catName;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Product> search_Item(String search) {
        try {
            ArrayList<Product> arrItem = new ArrayList<Product>();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            String query = "select * from item where itemName like '" + search + "%'";
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("itemId"));
                product.setProductName(rs.getString("itemName"));
                product.setCatID(rs.getString("catId"));
                product.setUnit(rs.getString("unit"));
                product.setQuantity(rs.getInt("quantityAvailable"));
                product.setQuantityEOD(rs.getInt("quantityEndOfDay"));
                product.setPrice(rs.getFloat("price"));
                arrItem.add(product);
            }
            st.close();
            con.close();
            return arrItem;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Product> search_Category(String search) {
        try {
            ArrayList<Product> arrItem = new ArrayList<Product>();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            String query = "select * from category inner join item on category.catId = item.catId where category.catName like '" + search + "%'";
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("itemId"));
                product.setProductName(rs.getString("itemName"));
                product.setCatID(rs.getString("catId"));
                product.setUnit(rs.getString("unit"));
                product.setQuantity(rs.getInt("quantityAvailable"));
                product.setQuantityEOD(rs.getInt("quantityEndOfDay"));
                product.setPrice(rs.getFloat("price"));
                arrItem.add(product);
            }
            st.close();
            con.close();
            return arrItem;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void runBath(ArrayList<String> arrStr) {
        try {
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            for (String s : arrStr) {
                st.addBatch(s);
            }
            st.executeBatch();
            st.close();
            con.close();

        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int countOrder() {
        try {
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            int count = 0;
            ResultSet rs = st.executeQuery("select orderId from Orders");
            while (rs.next()) {
                count++;
            }
            con.close();
            return count;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public int getUserId(String userName) {
        try {
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select userId from users where userName ='" + userName + "'");
            int userId = 0;
            while (rs.next()) {
                userId = rs.getInt("userId");
            }
            con.close();
            return userId;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;

    }

    public ArrayList<Order> getAllOrder() {
        try {
            ArrayList<Order> arrorder = new ArrayList<Order>();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from orders where type ='sell' and status = 'successed'");
            while (rs.next()) {
                Order o = new Order();
                o.setOrderID(rs.getInt("orderId"));
                o.setDatetime(rs.getDate("datetimes"));
                o.setUserId(rs.getInt("userId"));
                o.setType(rs.getString("type"));
                o.setTotal(rs.getFloat("total"));
                arrorder.add(o);
            }
            con.close();
            return arrorder;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Order getOrderbyId(String orderId) {
        try {
            Order order = new Order();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from orders where orderId ='" + orderId + "'");
            rs.next();
            order.setOrderID(rs.getInt("orderId"));
            order.setDatetime(rs.getDate("datetimes"));
            order.setUserId(rs.getInt("userId"));
            order.setType(rs.getString("type"));
            order.setTotal(rs.getFloat("total"));
            con.close();
            return order;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<OrderDetail> getOrderDetailByOrderId(String orderId) {
        try {
            ArrayList<OrderDetail> arrOrderDetail = new ArrayList<OrderDetail>();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from order_item where orderId ='" + orderId + "'");
            while (rs.next()) {
                OrderDetail orderDetail = new OrderDetail();
                Product pro = getProductById(rs.getInt("itemId"));
                orderDetail.setOrderID(rs.getInt("orderId"));
                orderDetail.setItemID(rs.getInt("itemId"));
                orderDetail.setItemName(pro.getProductName());
                orderDetail.setAmount(rs.getInt("amount"));
                orderDetail.setPriceSell(rs.getFloat("priceSell"));
                arrOrderDetail.add(orderDetail);
            }
            con.close();
            return arrOrderDetail;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    public Product getProductById(int itemId) {
        try {
            Product product = new Product();
            Connector condb = new Connector();
            Connection con = condb.connect();

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from item where itemId='" + itemId + "'");
            while (rs.next()) {
                product.setId(rs.getInt("itemId"));
                product.setProductName(rs.getString("itemName"));
                product.setCatID(rs.getString("catId"));
                product.setUnit(rs.getString("unit"));
                product.setQuantity(rs.getInt("quantityAvailable"));
                product.setQuantityEOD(rs.getInt("quantityEndOfDay"));
                product.setPrice(rs.getFloat("price"));
            }
            st.close();
            con.close();
            return product;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    public int getOrderId(int userId) {
        try {
            int orderId = 0;
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select top 1 orderId from orders  where userId =" + userId + " order by orderid desc");
            rs.next();
            orderId = rs.getInt("orderId");
            st.close();
            con.close();
            return orderId;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public String getUserName(int UserId) {
        try {

            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select userName from users where userId =" + UserId + "");
            rs.next();
            String username = rs.getString("userName");
            con.close();
            return username;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<User> getAllUser() {
        try {
            ArrayList<User> arrUser = new ArrayList<User>();
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from Users where position = 'sales manager' or position ='inventory manager'");
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("userId"));
                user.setPosition(rs.getString("position"));
                user.setUsername(rs.getString("userName"));
                user.setPassword(rs.getString("pass"));
                arrUser.add(user);
            }
            con.close();
            return arrUser;
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;

    }

    public void insertReport(String insertReport) {
        try {
            Connector condb = new Connector();
            Connection con = condb.connect();
            Statement st = con.createStatement();
            st.executeUpdate(insertReport);
            con.close();
        } catch (Exception ex) {
            Logger.getLogger(Function.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
